
DECLARE @UserNameTable TABLE 
	( 
		UserName nvarchar(100)
	)

insert into @UserNameTable
SELECT [name]
FROM sysusers 
where (isntuser = 1 or issqluser = 1) and ([name] <> 'dbo' and [name] <> 'guest')
-- For SQL 2005 or later
SELECT [name]
FROM sys.database_principals 
WHERE [type] in ('S', 'U') and [name] <> 'dbo' and [name] <> 'guest' and [name] <> 'INFORMATION_SCHEMA' and [name] <> 'sys'

--For SQL 2000
--SELECT [name]
--FROM sysusers 
--where (isntuser = 1 or issqluser = 1) and ([name] <> 'dbo' and [name] <> 'guest')


declare @name nvarchar(100)

WHILE (SELECT count(UserName) FROM @UserNameTable) > 0
BEGIN
	set @name = (select top 1 UserName from @UserNameTable)

--	When you use Auto_Fix, you must specify user and password if the login does not already exist, 
--	otherwise you must specify user but password will be ignored. login must be NULL. 
--	user must be a valid user in the current database. The login cannot have another user mapped to it.

	EXEC sp_change_users_login 'Auto_Fix', @name, NULL, @name

--Delete the first selected row in @UserNameTable
	DELETE from @UserNameTable where UserName = @name
END
go


